package com.wframe.project.jnd.dao;


import java.util.*;

import com.frame.util.DateConvert;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;
import com.alibaba.fastjson.JSON;
import com.frame.database.JdataBean;
import com.frame.database.PageResult;
import com.wframe.framework.database.BaseDao;
import com.wframe.framework.database.ParmSqlVo;
import com.wframe.framework.util.ConversionUtil;
import com.wframe.project.former.entity.ProductsAttrvalBean;
import com.wframe.project.jnd.vo.PriceBeanVo;
import org.springframework.transaction.annotation.Transactional;

@Repository
public class PurchaseTempDao extends BaseDao {

    public PageResult queryList(String userid, int size, int pageno) {
        return querySqlMap("select * from wb_buyplan where userid =? order by id desc", new Object[]{userid}, size, pageno);
    }

    public PageResult queryPlanProduct(String panid, String like, int size, int pageno) {
        String sql = "select (case when c.amount>(ifnull(t.amount,0)-ifnull(t.djamount,0)) then (ifnull(t.amount,0)-ifnull(t.djamount,0)) else c.amount end ) buyamount,c.id bid,t.id,t.productid,t.name,t.attrcodes,t.salesnum,t.popularity,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount,p.code productcode,p.name productname,p.status,p.unitname,p.attrvals,p.iscontrol,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_buyplan_product c"
                + " inner join wb_products_prices t on t.id=c.priceid"
                + " inner join wb_products p on p.id=c.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " where p.status<>-1 and c.panid=? order by c.id desc";
        PageResult pr = null;
        if (size == -1) {
            pr = new PageResult();
            pr.setList(querySqlMap(sql, new Object[]{panid}));
        } else {
            pr = querySqlMap(sql, new Object[]{panid}, size, pageno);
            List<Map> productlist = pr.getList();
            for (Map product : productlist) {
                String photo = product.get("photoview") == null ? "" : product.get("photoview").toString();
                if (StringUtils.isBlank(photo))
                    photo = product.get("product_photoview") == null ? "" : product.get("product_photoview").toString();
                product.put("photo", photo);
                String attrjson = product.get("attrvals") == null ? "" : product.get("attrvals").toString();
                if (StringUtils.isNotBlank(attrjson)) {
                    //将商品属性取出
                    try {
                        List<ProductsAttrvalBean> jsonarr = JSON.parseArray(attrjson, ProductsAttrvalBean.class);
                        for (ProductsAttrvalBean attrbean : jsonarr) {
                            product.put(attrbean.getAttrcode(), attrbean.getValtxt());
                        }
                    } catch (Exception ew) {
                        Logger.getLogger(PurchaseTempDao.class).error("JSONERROR-商品id:" + product.get("productid"), ew);
                    }
                }
            }
        }
        return pr;
    }

    public String saveBuyplan(JdataBean bean) throws Exception {
        if (StringUtils.isNotBlank(bean.getString("id"))) {
            Map keyMap = new HashMap();
            keyMap.put("id", bean.getString("id"));
            update("wb_buyplan", bean.getDataMap(), keyMap);
            return bean.getString("id");
        } else return saveReturnKey("wb_buyplan", bean.getDataMap());
    }

    public void delBuyplan(String userid, String id) {
        execute("delete from wb_buyplan where userid=? and id=?", new Object[]{userid, id});
    }

    public JdataBean getBuyplan(String userid, String id) {
        return getObject("select * from wb_buyplan where id=? and userid=?", new Object[]{id, userid});
    }

    public void addBuyplanProduct(String userid, String panid, List<PriceBeanVo> prices) {
        for (PriceBeanVo vo : prices) {
            int n = execute("update wb_buyplan_product set amount=amount+? where panid=? and priceid=?", new Object[]{vo.getBuyNum(), panid, vo.getPriceId()});
            if (n == 0)
                execute("insert into wb_buyplan_product(panid,productid,priceid,amount) select " + panid + ",productid,id," + vo.getBuyNum() + " from wb_products_prices where id=?", new Object[]{vo.getPriceId()});
        }
    }

    public void delBuyplanProduct(String userid, String panid) {
        execute("delete from wb_buyplan_product where panid=?", new Object[]{panid});
    }

    public void delBuyplanProduct(String userid, String panid, String priceid) {
        execute("delete from wb_buyplan_product where panid=? and priceid=?", new Object[]{panid, priceid});
    }

    public PageResult queryWebProduct(String like, int size, int pageno) {
        String sql = "select t.*,(ifnull(t.amount,0)-ifnull(t.djamount,0)) realamount,p.unitname,p.code productcode,p.name productname,p.attrvals,p.iscontrol,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_products_prices t"
                + " inner join wb_products p on p.id=t.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " where p.status=2";
        List patmlist = new ArrayList();
        if (StringUtils.isNotBlank(like)) {
            sql += " and (p.name like ? or t.name like ? or t.productid in (select b.productid from wb_products_attr a,wb_products_attrval b where a.id=b.attrid and a.code='B_chanjia' and b.val like ?))";
            patmlist.add("%" + like + "%");
            patmlist.add("%" + like + "%");
            patmlist.add("%" + like + "%");
        }
        sql += " order by t.salesnum,p.id desc";
        return querySqlMap2(sql, patmlist, size, pageno);
    }

    public void delRemin(String panid) {
        execute("update wb_buyplan set isremind=0,remindcon=null,remindphone=null,reminddata=null,remindrepeat=null,repeatday=null where id=?", new Object[]{panid});
    }

    public Object savePurchaseTemplate(Map map) {
        Object nextId = nextId("wb_buyplan");
        insert("wb_buyplan", map);
        return nextId;
    }

    public PageResult findTemplateProducts(String id, int pageSize, int pageNo) {
        String sql = "select (case when c.amount>(ifnull(t.amount,0)-ifnull(t.djamount,0)) then (ifnull(t.amount,0)-ifnull(t.djamount,0)) else c.amount end ) buyamount,t.id,t.productid,t.name,t.attrcodes,t.price,t.price_s,t.salesnum,t.popularity,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount,p.code productcode,p.name productname,p.status,p.unitname,p.attrvals,p.iscontrol,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_buyplan_product c"
                + " inner join wb_products_prices t on t.id=c.priceid"
                + " inner join wb_products p on p.id=c.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " where p.status<>-1 and c.panid=? order by c.id desc";
        return paginationAndCountList(sql, pageSize, pageNo, new Object[]{id});
    }

    public PageResult findTemplateProducts(String userid, String id, int pageSize, int pageNo) {
        String sql = "select (case when c.amount>(ifnull(t.amount,0)-ifnull(t.djamount,0)) then (ifnull(t.amount,0)-ifnull(t.djamount,0)) else c.amount end ) buyamount,c.id bid,t.id,t.productid,t.name,t.attrcodes,CASE WHEN u.price>0 THEN u.price else t.price END price,t.salesnum,t.popularity,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount,p.code productcode,p.name productname,p.status,p.unitname,p.attrvals,p.iscontrol,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_buyplan_product c"
                + " inner join wb_products_prices t on t.id=c.priceid"
                + " inner join wb_products p on p.id=c.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " left join (select pp.productid,pp.price from wbuser u join wb_products_pricegrade pp on u.pricegrade=pp.grade where u.id=?)u on c.productid=u.productid"
                + " where p.status<>-1 and c.panid=? order by c.id desc";
        PageResult pr = paginationAndCountList(sql, pageSize, pageNo, new Object[]{userid, id});
        List<Map> productlist = pr.getList();
        for (Map product : productlist) {
            String photo = product.get("photoview") == null ? "" : product.get("photoview").toString();
            if (StringUtils.isBlank(photo))
                photo = product.get("product_photoview") == null ? "" : product.get("product_photoview").toString();
            product.put("photo", photo);
            String attrjson = product.get("attrvals") == null ? "" : product.get("attrvals").toString();
            if (StringUtils.isNotBlank(attrjson)) {
                //将商品属性取出
                try {
                    List<ProductsAttrvalBean> jsonarr = JSON.parseArray(attrjson, ProductsAttrvalBean.class);
                    for (ProductsAttrvalBean attrbean : jsonarr) {
                        product.put(attrbean.getAttrcode(), attrbean.getValtxt());
                    }
                } catch (Exception ew) {
                    Logger.getLogger(PurchaseTempDao.class).error("JSONERROR-商品id:" + product.get("productid"), ew);
                }
            }
        }
        return pr;
    }

    @Transactional
    public List<JdataBean> findPlan() {
        String sql = "SELECT n.loginname,m.id,m.`name`,m.isremind,m.remindcon,m.isphone,m.remindphone,m.reminddata,m.remindrepeat,m.repeatday from wb_buyplan m"
                + " JOIN wbuser n ON m.userid=n.id"
                + " WHERE m.isremind=1 and m.isphone=1 and m.reminddata between ? and ?";
        Calendar ca = Calendar.getInstance();
        ca.add(Calendar.MINUTE, 10);
        return list(sql, new Object[]{DateConvert.toDefaultString(new Date()), DateConvert.toDefaultString(ca.getTime())});
    }

    public PageResult findWebProducts(String userId, String like, int pageSize, int pageNo) {
        String sql = "select t.*,CASE WHEN u.price>0 THEN u.price else t.price END realprice,(ifnull(t.amount,0)-ifnull(t.djamount,0)) realamount,p.unitname,p.code productcode,p.name productname,p.attrvals,p.iscontrol,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_products_prices t"
                + " inner join wb_products p on p.id=t.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " left join (select pp.productid,pp.price from wbuser u join wb_products_pricegrade pp on u.pricegrade=pp.grade where u.id=?)u on t.productid=u.productid"
                + " where p.status=2";
        List<String> patmlist = new ArrayList<>();
        patmlist.add(userId);
        if (StringUtils.isNotBlank(like)) {
            sql += " and (p.name like ? or t.name like ? or t.productid in (select b.productid from wb_products_attr a,wb_products_attrval b where a.id=b.attrid and a.code='B_chanjia' and b.val like ?))";
            patmlist.add("%" + like + "%");
            patmlist.add("%" + like + "%");
            patmlist.add("%" + like + "%");
        }
        sql += " order by t.salesnum,p.id desc";
        return paginationAndCountList(sql, pageSize, pageNo, patmlist);
    }

    public void update(PriceBeanVo bean) {
        String sql = "INSERT into wb_buyplan_product(id,amount)VALUES(?,?)on DUPLICATE KEY update amount=VALUES(amount)";
        operate(sql, new Object[]{bean.getPriceId(), bean.getBuyNum()});
    }

    public boolean checkOwned(String panid, Object productid) {
        String sql = "SELECT EXISTS(SELECT 1 from wb_buyplan_product WHERE panid=? and productid=?)";
        int isExist = Integer.parseInt(String.valueOf(uniqueColumnResult(sql, new Object[]{panid, productid})));
        return isExist == 1;
    }
}
